import db from '@/common/sqlite.js'
import pinyin from '@/common/pingyinUtils.js'

async function openDB() {
	if (!db.isOpen()) {
		await db.open()
	}
}

async function closeDB() {
	if (db.isOpen()) {
		await db.close()
	}
}

/*
	@Description: 年度收支统计
	year string
*/
export async function getAllAccounts(year) {
	await openDB()
	const monArr = [],
		list = []
	for (let i = 1; i <= 12; i++) {
		monArr.push(i.toString().padStart(2, '0'))
	}
	const paySql =
		`SELECT ROUND(sum(num),2) AS 'mon_pay',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 0 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
	const inSql =
		`SELECT ROUND(sum(num),2) AS 'mon_in',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 1 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
	const payList = await db.selectTableDataBySql(paySql);
	const inList = await db.selectTableDataBySql(inSql);
	// console.log(payList,inList)
	for (let mon of monArr) {
		list.push({
			mon: mon,
			pay: (payList.filter(v => v.mon == mon))[0]?.mon_pay || 0,
			in: (inList.filter(v => v.mon == mon))[0]?.mon_in || 0
		})
	}
	return list
	// console.log(list)
	await closeDB()
}

/*
	@Description: 总收支统计
*/
export async function getTotalAccounts() {
	await openDB()
	const list = []
	const paySql =
		`SELECT ROUND(sum(num),2) AS 'year_pay',STRFTIME('%Y', tb_record.create_time) AS 'year' from tb_type , tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 0 GROUP BY year ORDER BY year desc`;
	const inSql =
		`SELECT ROUND(sum(num),2) AS 'year_in',STRFTIME('%Y', tb_record.create_time) AS 'year' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 1 GROUP BY year ORDER BY year desc`;
	const payList = await db.selectTableDataBySql(paySql);
	const inList = await db.selectTableDataBySql(inSql);
	const yearArr = (await getAllYear()).reverse()
	// console.log(payList,inList)
	for (let year of yearArr) {
		list.push({
			year,
			pay: (payList.filter(v => v.year == year))[0]?.year_pay || 0,
			in: (inList.filter(v => v.year == year))[0]?.year_in || 0
		})
	}
	return list
	// console.log(list)
	await closeDB()
}


export async function deleteAsset(id) {
	await openDB()
	try {
		// 删除asset
		await db.deleteTableData('tb_asset', 'id', id)
		uni.showToast({
			title: "删除成功"
		})

	} catch (e) {
		uni.showToast({
			title: "删除失败",
			icon: 'fail'
		})
		//TODO handle the exception
	}
	await closeDB()
}

// 查询 净资产、资产、负债的总计
export async function getTotalAsset() {
	await openDB()
	const monArr = [],
		list = []
	for (let i = 1; i <= 12; i++) {
		monArr.push(i.toString().padStart(2, '0'))
	}
	const paySql =
		`SELECT ROUND(sum(num),2) AS 'mon_pay',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 0 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
	const inSql =
		`SELECT ROUND(sum(num),2) AS 'mon_in',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 1 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
	const payList = await db.selectTableDataBySql(paySql);
	const inList = await db.selectTableDataBySql(inSql);
	// console.log(payList,inList)
	for (let mon of monArr) {
		list.push({
			mon: mon,
			pay: (payList.filter(v => v.mon == mon))[0]?.mon_pay || 0,
			in: (inList.filter(v => v.mon == mon))[0]?.mon_in || 0
		})
	}
	return list
	// console.log(list)
	await closeDB()

}


export async function addAsset(data, showToast = true) {
	await openDB()
	if (data.remark === undefined) {
		data.remark = '';
	}
	const col = `'type_id','num','create_time','remark','title'`
	const dataSql = `(${data.type_id},${data.num},'${data.create_time}','${data.remark}','${data.title}')`
	try {
		await db.insertTableData('tb_asset', dataSql, col)
		if (showToast) {
			await uni.showToast({
				title: '添加完成',
				duration: 1500
			})
		}
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: '记录失败',
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}

export async function updateAsset(data, showToast = true) {
	console.log('updateAsset::', data)
	try {
		await openDB()
		const dataSql =
			`type_id = ${data.type_id},num = ${data.num},create_time = '${data.create_time}',remark = '${data.remark}',title = '${data.title}'`
		await db.updateTableData('tb_asset', dataSql, 'id', data.id)
		uni.showToast({
			title: '编辑成功'
		})
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: '编辑失败',
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
export async function changeAsset(data) {
	try {
		await openDB()
		console.log('changeAsset', data)
		// 给表 "products" 中 id=1001 的记录的 quantity 增加 5
		await db.updateFieldValue(
			"tb_asset",
			"id",
			data.id,
			"num",
			data.num
		);

	} catch (error) {
		console.log('changeAsset::error', error)
		uni.showToast({
			title: error.message,
			icon: "none"
		});
	}
	await closeDB()
}
/*
	@Description: 添加收支记录
	@ obj Object
*/
export async function addRecord(data, showToast = true) {
	await openDB()
	const col = `'type_id','asset_type_id','num','create_time','remark','image'`
	const dataSql =
		`(${data.type_id},${data.asset_type_id},${data.num},'${data.create_time}','${data.remark}','${data.image ?? ''}')`
	try {
		await db.insertTableData('tb_record', dataSql, col)
		showToast && uni.showToast({
			title: '记录成功'
		})
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: '记录失败',
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
export async function importRecords(records, showToast = true) {
	await openDB()

	const col = `'type_id','num','create_time','remark','import_id','image'`
	let values = records.map(data =>
		`(${data.type_id},${data.num},'${data.create_time}','${data.remark}','${data.import_id}','${data.image ?? ''}')`
	).join(',')

	try {
		await db.insertTableData('tb_record', values, col)
		if (showToast) {
			uni.showToast({
				title: '导入成功'
			})
		}
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: '导入失败',
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}

	await closeDB()
}

/*
	@Description: 编辑记录
	@ obj Object
*/
export async function updateRecord(data) {
	await openDB()
	const dataSql =
		`type_id = ${data.type_id},num = ${data.num},create_time = '${data.create_time}',remark = '${data.remark}',image='${data.image}'`
	try {
		await db.updateTableData('tb_record', dataSql, 'id', data.id)
		uni.showToast({
			title: '编辑成功'
		})
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: '编辑失败',
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}

/*
	@Description: 删除记录
	@ id number
*/
export async function delRecord(id) {
	// console.log(id)
	await openDB()
	try {
		await db.deleteTableData('tb_record', 'id', id)
		uni.showToast({
			title: "删除成功"
		})
	} catch (e) {
		// console.log(JSON.stringify(e))
		uni.showToast({
			title: "删除失败",
			icon: 'fail'
		})
		//TODO handle the exception
	} finally {
		await closeDB()
	}
}

export async function deleteRecordByImportIds(importIds) {
	if (importIds == undefined || importIds.length == 0) {
		uni.showToast({
			title: "删除明细为空",
			icon: 'fail'
		})
		return
	}

	await openDB()
	try {
		let importIdsWithQuotes = importIds.map(id => `'${id}'`);
		let sql = `DELETE FROM tb_record WHERE import_id IN (${importIdsWithQuotes.join(",")})`;

		console.log('批量删除sql', sql)
		await db.deleteTableBySql('tb_record', sql)
		uni.showToast({
			title: "删除成功"
		})
	} catch (e) {
		// console.log(JSON.stringify(e))
		uni.showToast({
			title: "删除失败",
			icon: 'fail'
		})
		//TODO handle the exception
	} finally {
		await closeDB()
	}
}

// 获取首页数据
export async function getHome(currentMonth, type_id) {
	const homeData = {
		curMonPay: 0, //本月支出
		curMonIn: 0, //本月收入
		surplus: 0, //本月结余
		list: [] //收支明细
	}
	try {
		await openDB();
		// const date = new Date()
		// const year = date.getFullYear()
		// const mon = (date.getMonth()+1).toString().padStart(2,'0')
		// const currentMonth = year+'-'+mon
		// console.log(await db.selectTableData('tb_record'))
		// const sql = `ALTER TABLE tb_type MODIFY COLUMN 'is_custom' integer DEFAULT ''`
		// await db.ExecuteSQL(sql)

		let condition = ' ';
		if (type_id != null) {
			condition += `AND tb_record.type_id = ${type_id}`
		}

		const currentMonthSql =
			`SELECT DISTINCT STRFTIME('%m-%d', tb_record.create_time) AS 'date' FROM tb_record  WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' ${condition} ORDER BY tb_record.create_time DESC`
		const curMonDayAccounts =
			`SELECT tb_record.id,tb_record.type_id,tb_record.asset_type_id,tb_type.icon,tb_type.title,tb_record.num , tb_type.type,STRFTIME('%m-%d', tb_record.create_time) AS 'date', STRFTIME('%H:%M:%S', tb_record.create_time) AS 'time',tb_record.remark,tb_record.image  FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}'  ${condition}  and tb_type.id = tb_record.type_id ORDER BY date DESC, time DESC`

		const dateList = await db.selectTableDataBySql(currentMonthSql);
		if (dateList.length === 0) {
			return homeData
		}
		const accounts = await db.selectTableDataBySql(curMonDayAccounts);
		for (let date of dateList) {
			const obj = {
				date: '',
				list: '',
			}
			obj.date = date.date
			const arr = [],
				dateIn = 0,
				datePay = 0;
			for (let acc of accounts) {
				if (date.date === acc.date) {
					if (acc.type === 0) {
						homeData.curMonPay += acc.num
					} else if (acc.type === 1) {
						homeData.curMonIn += acc.num
					}
					arr.push(acc)
				}
			}
			obj.dateIn = dateIn
			obj.datePay = datePay
			obj.list = arr
			homeData.list.push(obj)
		}
		// const paySql = `SELECT ROUND(sum(num),2) as sum FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' AND tb_type.id = tb_record.type_id AND tb_type.type=0`
		// const payNum = await db.selectTableDataBySql(paySql)
		// const inSql = `SELECT ROUND(sum(num),2) as sum FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' AND tb_type.id = tb_record.type_id AND tb_type.type=1`
		// const inNum = await db.selectTableDataBySql(inSql)
		// homeData.curMonPay = payNum[0].sum || 0
		// homeData.curMonIn = inNum[0].sum || 0

		await closeDB()
		return homeData
	} catch (e) {
		return homeData
		//TODO handle the exception
	}

}

/*
	@Description:图表页,图表
	@type: number 类型 1收入 0支出
	@dateType: 日期类型 0周,1月,2年
	@dateValue: string 今年第几周 /月 01，02.||年 2024
*/
export async function getLine(type, dateType, dateValue) {
	// console.log(dateValue)
	await openDB()
	try {
		let dateCondition = '',
			groupCondition = ''
		switch (dateType) {
			case 0: {
				dateCondition = `AND STRFTIME('%Y-%W', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'week'
			}
			break;
			case 1: {
				dateCondition = `AND STRFTIME('%Y-%m', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'date'
			}
			break;
			case 2: {
				dateCondition = `AND STRFTIME('%Y', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'mon'
			}
			break;
		}
		const sql =
			`SELECT ROUND(sum(num),2) AS num, STRFTIME('%m', tb_record.create_time) as mon, STRFTIME('%m-%d', tb_record.create_time) as date, STRFTIME('%w', tb_record.create_time) as week FROM tb_record INNER JOIN tb_type ON tb_type.id = tb_record.type_id AND tb_type.type=${type} ${dateCondition} GROUP BY ${groupCondition} ORDER BY tb_record.create_time`
		// console.log(sql)
		const list = await db.selectTableDataBySql(sql)
		// console.log(list)
		return list
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}


export async function getLineByRange(type, dateType, dateValue) {
	await openDB()
	try {
		let dateCondition = '',
			groupCondition = ''
		if (dateType === 'year') {
			dateCondition +=
				` AND STRFTIME('%Y', tb_record.create_time) in (${dateValue.map(str => `'${str}'`).join(",")}) `
			groupCondition = 'year'
		}
		if (dateType === 'month') {
			dateCondition +=
				` AND STRFTIME('%Y-%m', tb_record.create_time) in (${dateValue.map(str => `'${str}'`).join(",")}) `
			groupCondition = 'month'
		}
		if (dateType === 'date') {
			dateCondition +=
				` AND STRFTIME('%Y-%m-%d', tb_record.create_time) in (${dateValue.map(str => `'${str}'`).join(",")}) `
			groupCondition = 'date'
		}

		const sql =
			`SELECT ROUND(sum(num),2) AS num,STRFTIME('%Y-%m', tb_record.create_time) as month,STRFTIME('%Y-%m-%d', tb_record.create_time) as date, STRFTIME('%Y', tb_record.create_time) as year,tb_record.create_time as createTime FROM tb_record INNER JOIN tb_type ON tb_type.id = tb_record.type_id AND tb_type.type=${type} ${dateCondition} GROUP BY ${groupCondition}  ORDER BY ${groupCondition} `
		let list = await db.selectTableDataBySql(sql)
		return list;
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}


export async function getRank(type, year, month, day) {
	await openDB()
	try {

		let dateCondition = ''
		if (year) {
			dateCondition += ` AND STRFTIME('%Y', tb_record.create_time) = '${year}' `
		}
		if (month) {
			month = month.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%m', tb_record.create_time)  = '${month}' `
		}
		if (day) {
			day = day.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%d', tb_record.create_time) = '${day}' `
		}
		const sql =
			`SELECT ROUND(sum(num),2) AS 'num' ,COUNT(num) AS count,tb_type.icon, tb_type.title,tb_type.id AS type_id FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.type = ${type} ${dateCondition} GROUP BY tb_type.title ORDER BY num DESC`
		return db.selectTableDataBySql(sql)
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}


// 求累计记账数
export async function getTotalRecordsNum() {
	try {
		await openDB();
		let sql = `SELECT count(1) as num from tb_record`
		return db.selectTableDataBySql(sql);

	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}

// 求累计记账天数
export async function getTotalRecordDateNum() {
	try {
		await openDB();
		let sql =
			`SELECT distinct STRFTIME('%Y%m%d',tb_record.create_time)  as date  from tb_record order by create_time asc`
		return db.selectTableDataBySql(sql);

	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}



export async function getRankByDate(type, year, month, day, pageNo, pageSize) {
	await openDB()
	try {

		let dateCondition = ''
		if (year) {
			dateCondition += ` AND STRFTIME('%Y', tb_record.create_time) = '${year}' `
		}
		if (month) {
			month = month.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%m', tb_record.create_time)  = '${month}' `
		}
		if (day) {
			day = day.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%d', tb_record.create_time) = '${day}' `
		}

		let sql = ''
		if (pageNo != null && pageSize != null) {
			sql =
				`SELECT tb_record.create_time, ROUND(num,2) AS num,tb_record.remark ,image,tb_type.type,tb_type.icon, tb_type.title,tb_type.id AS type_id FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.type = ${type} ${dateCondition}  ORDER BY num DESC LIMIT ${pageNo},${pageSize}`
		} else {
			sql =
				`SELECT tb_record.create_time, ROUND(num,2) AS num,tb_record.remark ,image,tb_type.type,tb_type.icon, tb_type.title,tb_type.id AS type_id FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.type = ${type} ${dateCondition}  ORDER BY num DESC `
		}


		console.log(sql)
		return db.selectTableDataBySql(sql)
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}

// 获取记录中的年份列表
export async function getAllYear() {
	try {
		await openDB()
		const sql =
			`SELECT DISTINCT STRFTIME('%Y', tb_record.create_time) AS year FROM tb_record ORDER BY STRFTIME('%Y', tb_record.create_time)`
		const yearList = await db.selectTableDataBySql(sql)
		return yearList.map(obj => obj.year)
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}

/*
	@Description: 图表页详情，获取收支排行榜item详细
	@type_id Number: 收支分类
	@dateType: 日期类型 0周,1月,2年
	@dateValue: string 今年第几周 /月 01，02.||年 2024
*/
export async function getRankDetail(type_id, year, month, day) {
	await openDB()
	try {
		let dateCondition = ''
		if (year) {
			dateCondition += ` AND STRFTIME('%Y', tb_record.create_time) = '${year}' `
		}
		if (month) {
			month = month.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%m', tb_record.create_time)  = '${month}' `
		}
		if (day) {
			day = day.toString().padStart(2, '0')
			dateCondition += ` AND STRFTIME('%d', tb_record.create_time) = '${day}' `
		}
		const sql =
			`SELECT num ,remark,image,tb_type.icon,tb_type.type, tb_type.title,tb_type.id,STRFTIME('%m-%d', tb_record.create_time) AS mon,STRFTIME('%H:%M:%S', tb_record.create_time) AS time FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.id = ${type_id} ${dateCondition} ORDER BY tb_record.create_time DESC`
		console.log(sql)
		const list = db.selectTableDataBySql(sql);
		console.log(list)
		return list
	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}

// 获取type 列表
export async function getTypeList() {
	await openDB()
	try {
		const list = await db.selectTableDataBySql('select t.* from tb_type t order by t.sort asc')
		return {
			code: 1,
			list
		}
	} catch (e) {

	} finally {
		await closeDB()
	}
}

export async function getRecordList() {
	await openDB()
	try {
		const list = await db.selectTableData('tb_record')
		return {
			code: 1,
			list
		}
	} catch (e) {

	} finally {
		await closeDB()
	}
}

/*
	@Description: 批量导入数据，
		1.导入自定义类型，
			如果存在，不导入，修改recordList type外键
			如果不存在，导入，修改recordList type外键
		2.导入record
			如果存在，不导入
			如果不存在，导入
*/
export async function batchImport(data) {
	// console.log('shuju',data)
	if (JSON.stringify(data) == '{}') {
		uni.showToast({
			title: '无数据',
			icon: 'error'
		})
		return
	}
	// 期望导入的数据
	const {
		customTypeList,
		recordList
	} = data
	// console.log(customTypeList,recordList)
	if (customTypeList.length == 0 && recordList.length == 0) {
		uni.showToast({
			title: '无数据',
			icon: 'error'
		})
		return
	}
	uni.showLoading({
		title: '正在导入...'
	})
	// 获取已存在自定义类型列表
	let typeArr = await getCustomTypeList()
	// 获取已存在记录列表
	const recordArr = (await getRecordList()).list
	// 获取应该导入的自定义类型列表,即不存在的自定义类型
	let shouldInType = customTypeList.filter(expectItem => {
		for (let item of typeArr) {
			if (item.title === expectItem.title) {
				return false
			}
		}
		return true
	})
	// 导入自定义类型
	for (let item of shouldInType) {
		await addCustomType(item, false)
	}
	// 重新赋值id，修改recordListId值
	typeArr = await getCustomTypeList()
	shouldInType = shouldInType.map(item => {
		for (let nItem of typeArr) {
			if (item.title === nItem.title && item.type === nItem.type) {
				item.newId = nItem.id
				break
			}
		}
		return item
	})
	// console.log(shouldInType)
	// 获取应该导入的记录列表，即不存在的记录
	let shouldInRecord = recordList.filter(expectItem => {
		for (let item of recordArr) {
			if (item.create_time === expectItem.create_time && item.num === expectItem.num) {
				return false
			}
		}
		return true
	})
	// 修改自定义type_id
	shouldInRecord = shouldInRecord.map(item => {
		for (let itemType of shouldInType) {
			if (item.type_id == itemType.id) {
				item.type_id = itemType.newId
				break
			}
		}
		return item
	})
	// 添加record记录
	for (let item of shouldInRecord) {
		await addRecord(item, false)
	}
	uni.hideLoading()
	uni.showToast({
		title: '导入成功'
	})
}

// 添加自定义收支类型
/*
	@obj object {title:'',type:0||1}
*/
export async function addCustomType(obj, showToast = true) {
	await openDB()
	try {
		// 添加自定义类型
		const col = "'title','icon','type','is_custom',sort"
		const dataStr = `('${obj.title}','icon-shouye',${obj.type},1,9999)`;
		await db.insertTableData('tb_type', dataStr, col)
		showToast && uni.showToast({
			title: "添加成功"
		})
	} catch (e) {
		//TODO handle the exception
		uni.showToast({
			title: "添加失败",
			icon: 'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}

/*
	修改自定义类型
*/
export async function updateCustomType(obj) {
	await openDB()
	try {
		await db.updateTableData('tb_type', `title='${obj.title}'`, 'id', obj.id)
		uni.showToast({
			title: "修改成功"
		})
	} catch (e) {
		uni.showToast({
			title: "修改失败",
			icon: 'fail'
		})
		//TODO handle the exception
	}
	await closeDB()
}

/*
	更新类目顺序
*/
export async function updateTypeSort(obj) {
	await openDB()
	try {
		await db.updateTableData('tb_type', `sort='${obj.sort}'`, 'id', obj.id)

	} catch (e) {
		console.log(e)
		//TODO handle the exception
	}
	await closeDB()
}

export async function deleteCustomType(id) {
	await openDB()
	try {
		// 删除records记录
		await db.deleteTableData('tb_record', 'type_id', id)
		// 删除type
		await db.deleteTableData('tb_type', 'id', id)
		uni.showToast({
			title: "删除成功"
		})

	} catch (e) {
		uni.showToast({
			title: "删除失败",
			icon: 'fail'
		})
		//TODO handle the exception
	}
	await closeDB()
}

export async function getCustomTypeList() {
	await openDB()
	try {
		return await db.selectTableData('tb_type', 'is_custom', 1)
	} catch (e) {
		//TODO handle the exception
		console.log(JSON.stringify(e))
	}
	await closeDB()
}

// 创建收支类型表,并插入初始数据
export async function createType() {
	await openDB();
	// 删除原数据表
	try {
		await db.dropTable('tb_type')
	} catch (e) {
		//TODO handle the exception
		console.log(e)
		return false
	}
	// 创建类型表
	const data =
		'"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"title" TEXT,"icon" TEXT,"type" integer,"sort" integer DEFAULT 0,"is_custom" integer DEFAULT 0';
	try {
		await db.createTable('tb_type', data)
	} catch (e) {
		//TODO handle the exception
		console.log(e)
		return false
	}
	// 字段
	const col = "'id','title','icon','type','sort'"
	// 数据
	const payType = [{
		id: 1,
		title: '餐饮',
		icon: 'icon-canyin',
		sort: 0
	}, {
		id: 2,
		title: '购物',
		icon: 'icon-gouwu',
		sort: 1
	}, {
		id: 3,
		title: '日用',
		icon: 'icon-riyongbaihuo',
		sort: 2
	}, {
		id: 4,
		title: '交通',
		icon: 'icon-jiaotong',
		sort: 3
	}, {
		id: 5,
		title: '蔬菜',
		icon: 'icon-shucai',
		sort: 4
	}, {
		id: 6,
		title: '水果',
		icon: 'icon-shuiguo',
		sort: 5
	}, {
		id: 7,
		title: '零食',
		icon: 'icon-lingshi',
		sort: 6
	}, {
		id: 8,
		title: '运动',
		icon: 'icon-yundong',
		sort: 7
	}, {
		id: 9,
		title: '娱乐',
		icon: 'icon-yule',
		sort: 8
	}, {
		id: 10,
		title: '服饰',
		icon: 'icon-fushi',
		sort: 9
	}, {
		id: 11,
		title: '化妆品',
		icon: 'icon-icon-test',
		sort: 10
	}, {
		id: 12,
		title: '住房',
		icon: 'icon-zhufang',
		sort: 11
	}, {
		id: 14,
		title: '聚会',
		icon: 'icon-080juhui',
		sort: 13
	}, {
		id: 15,
		title: '旅行',
		icon: 'icon-lvhang',
		sort: 14
	}, {
		id: 16,
		title: '数码',
		icon: 'icon-shuma',
		sort: 15
	}, {
		id: 17,
		title: '汽车',
		icon: 'icon-xiaoqiche',
		sort: 16
	}, {
		id: 18,
		title: '医疗',
		icon: 'icon-yiliao',
		sort: 17
	}, {
		id: 19,
		title: '学习',
		icon: 'icon-xuexi',
		sort: 18
	}, {
		id: 20,
		title: '红包',
		icon: 'icon-hongbao',
		sort: 19
	}, {
		id: 22,
		title: '维修',
		icon: 'icon-weixiu',
		sort: 21
	}, {
		id: 23,
		title: '公益',
		icon: 'icon-aixinjuanzeng',
		sort: 22
	}, {
		id: 24,
		title: '快递',
		icon: 'icon-kuaidi',
		sort: 23
	}, {
		id: 25,
		title: '支付宝',
		icon: 'icon-zhifubaozhifu',
		sort: 24
	}, {
		id: 26,
		title: '微信支付',
		icon: 'icon-weixinzhifu',
		sort: 25
	}, {
		id: 27,
		title: '转账',
		icon: 'icon-zhuanzhang',
		sort: 26
	}]
	const incomeType = [{
		id: 28,
		title: '工资',
		icon: 'icon-gongzika',
		sort: 0
	}, {
		id: 29,
		title: '兼职',
		icon: 'icon-jianzhi',
		sort: 1
	}, {
		id: 31,
		title: '红包',
		icon: 'icon-hongbao',
		sort: 3
	}, {
		id: 32,
		title: '转账',
		icon: 'icon-zhuanzhang',
		sort: 4
	}, {
		id: 33,
		title: '支付宝',
		icon: 'icon-zhifubaozhifu',
		sort: 5
	}, {
		id: 34,
		title: '微信支付',
		icon: 'icon-weixinzhifu',
		sort: 6
	}]


	const dataStr = (payType.map(item => `('${item.id}','${item.title}','${item.icon}',0,'${item.sort}')`)).join(
		',') + ',' + (
		incomeType.map(item => `('${item.id}','${item.title}','${item.icon}',1,'${item.sort}')`)).join(',')
	// 插入类型数据
	try {
		await db.insertTableData('tb_type', dataStr, col)
	} catch (e) {
		//TODO handle the exception
		console.log(JSON.stringify(e))
		return false
	}
	return true

}

// 创建记录表
async function createRecord() {
	// 删除原数据表
	try {
		await db.dropTable('tb_record')
	} catch (e) {
		//TODO handle the exception
		return false
	}
	// 创建记录表
	const data =
		`"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"type_id" INTEGER,"asset_type_id" INTEGER,"num" real,"create_time" text,"remark" TEXT,"import_id" TEXT, "image" blob DEFAULT ''`
	try {
		await db.createTable('tb_record', data)
	} catch (e) {
		//TODO handle the exception
		return false
	}
	return true
}

// 创建资产管理
async function createAsset() {
	// 删除原数据表
	try {
		await db.dropTable('tb_asset')

		const data =
			`"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"type_id" INTEGER,"num" real,"create_time" text,"remark" TEXT,"title" TEXT`;
		await db.createTable('tb_asset', data)
	} catch (e) {
		console.log(e)
		//TODO handle the exception
		return false
	}
	return true
}

// 查全部资产信息
export async function getAssetList() {
	try {
		await openDB();
		let sql =
			`SELECT tb_asset.id,tb_asset.num,tb_asset.remark,tb_asset.title,tb_asset.create_time,tb_asset.type_id,t1.parent,t1.icon,t1.type,t1.type_title,ifnull(t2.type_title,t1.type_title) as parent_title,ifnull(t2.icon,t1.icon) as parent_icon,ifnull(t2.id,t1.id) as parent_type_id  from tb_asset left join tb_asset_type t1 on t1.id = tb_asset.type_id left join tb_asset_type t2 on t2.id = t1.parent  WHERE 1=1 order by tb_asset.num desc  `
		return db.selectTableDataBySql(sql);

	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}
// 查全部资产信息
export async function getAssetTotal() {
	let obj = {
		total: 0,
		inTotal: 0,
		outTotal: 0
	}

	try {
		await openDB();
		let sql =
			`select sum(t.num) as num , t1.type from tb_asset t left join tb_asset_type t1 on t1.id = t.type_id group by t1.type`
		let data = await db.selectTableDataBySql(sql);
		console.log(data)

		if (data != null && data.length > 0) {
			data.forEach(item => {
				if (item.type === 0) {
					obj.outTotal += item.num
				} else {
					obj.inTotal += item.num
				}

			})
		}

		obj.total = obj.inTotal - obj.outTotal
		return obj;
	} catch (e) {
		//TODO handle the exception
		return obj
	} finally {
		await closeDB()
	}
}


async function createAssetType() {
	// 删除原数据表
	try {
		await db.dropTable('tb_asset_type')

		// 创建记录表
		const data =
			`"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"parent" INTEGER DEFAULT 0, "type_title" text,"create_time" text,"remark" TEXT,"icon" text,"type" INTEGER,"sort" INTEGER,"pinyin" text`
		await db.createTable('tb_asset_type', data)


		// 字段
		// type 0 为负数 1为正数
		const col = "'id','type_title','icon',create_time,type,sort,parent,pinyin"
		const typeList = [{
				id: 1,
				name: '现金',
				icon: 'icon-color-_xianjin',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 1,
				parent: 0
			},
			{
				id: 2,
				name: '银行卡',
				icon: 'icon-color-yinhangqiaguanli',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 2,
				parent: 0
			},
			{
				id: 3,
				name: '网银',
				icon: 'icon-color-shoujiyinhang',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 3,
				parent: 0
			}, {
				id: 4,
				name: '负债',
				icon: 'icon-color-fuzhai',
				create_time: '2024-03-20 10:31:31',
				type: 0,
				sort: 4,
				parent: 0
			}, {
				id: 5,
				name: '公积金',
				icon: 'icon-color-gongjijinchaxun',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 5,
				parent: 0
			}, {
				id: 6,
				name: '其它资产',
				icon: 'icon-color-zichan',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 6,
				parent: 0
			},
			{
				id: 38,
				name: '借入',
				icon: 'icon-jieru',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 6
			},
			{
				id: 39,
				name: '借出',
				icon: 'icon-jiechu',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 6
			},
			{
				id: 40,
				name: '押金',
				icon: 'icon-icon-yajinguanli',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 6
			},
			{
				id: 41,
				name: '会员卡',
				icon: 'icon-huiyuanka',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 6
			},
			{
				id: 42,
				name: '自定义',
				icon: 'icon-color-_xianjin',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 6
			},
			{
				id: 7,
				name: '支付宝',
				icon: 'icon-color-zhifubao',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 8,
				name: '微信',
				icon: 'icon-color-weixin',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 34,
				name: '抖音',
				icon: 'icon-douyin',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 35,
				name: '京东',
				icon: 'icon-jingdong',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 36,
				name: '花呗',
				icon: 'icon-huabei',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 37,
				name: '美团',
				icon: 'icon-huabei',
				create_time: '2024-03-20 10:31:31',
				type: 1,
				sort: 7,
				parent: 3
			},
			{
				id: 9,
				name: '中国银行',
				icon: 'icon-color-yinhang-zhongguoyinhang',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 9,
				parent: 2
			}, {
				id: 10,
				name: '光大银行',
				icon: 'icon-color-yinhang-guangda',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 10,
				parent: 2
			}, {
				id: 11,
				name: '工商银行',
				icon: 'icon-color-yinhang-gongshang',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 11,
				parent: 2
			}, {
				id: 12,
				name: '招商银行',
				icon: 'icon-color-yinhang-zhaoshang',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 12,
				parent: 2
			}, {
				id: 13,
				name: '汇丰银行',
				icon: 'icon-color-yinhang-huifeng',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 13,
				parent: 2
			}, {
				id: 14,
				name: '民生银行',
				icon: 'icon-color-yinhang-minsheng',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 14,
				parent: 2
			}, {
				id: 15,
				name: '华夏银行',
				icon: 'icon-color-yinhang-huaxia',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 15,
				parent: 2
			}, {
				id: 16,
				name: '建设银行',
				icon: 'icon-color-yinhang-jianshe',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 16,
				parent: 2
			}, {
				id: 17,
				name: '交通银行',
				icon: 'icon-color-yinhang-jiaotong',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 17,
				parent: 2
			}, {
				id: 18,
				name: '兴业银行',
				icon: 'icon-color-yinhang-xingye',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 18,
				parent: 2
			}, {
				id: 19,
				name: '浦发银行',
				icon: 'icon-color-yinhang-pufa',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 19,
				parent: 2
			}, {
				id: 20,
				name: '农行银行',
				icon: 'icon-color-yinhang-nonghang',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 20,
				parent: 2
			}, {
				id: 21,
				name: '渣打银行',
				icon: 'icon-color-yinhang-zhada',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 21,
				parent: 2
			}, {
				id: 22,
				name: '花旗银行',
				icon: 'icon-color-yinhang-huaqi',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 22,
				parent: 2
			}, {
				id: 23,
				name: '东亚银行',
				icon: 'icon-color-yinhang-dongya',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 23,
				parent: 2
			}, {
				id: 24,
				name: '恒丰银行',
				icon: 'icon-color-yinhang-hengfeng',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 24,
				parent: 2
			}, {
				id: 25,
				name: '上海银行',
				icon: 'icon-color-yinhang-shanghai',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 25,
				parent: 2
			}, {
				id: 26,
				name: '南京银行',
				icon: 'icon-color-yinhang-nanjing',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 26,
				parent: 2
			}, {
				id: 27,
				name: '浙商银行',
				icon: 'icon-color-yinhang-zheshang',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 27,
				parent: 2
			}, {
				id: 28,
				name: '杭州银行',
				icon: 'icon-color-yinhang-hangzhou',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 28,
				parent: 2
			}, {
				id: 29,
				name: '苏宁银行',
				icon: 'icon-color-yinhang-suning',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 29,
				parent: 2
			}, {
				id: 30,
				name: '邮政储蓄银行',
				icon: 'icon-color-yinhang-youzhengchuxu',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 30,
				parent: 2
			}, {
				id: 31,
				name: '平安银行',
				icon: 'icon-color-yinhang-pingan',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 31,
				parent: 2
			}, {
				id: 32,
				name: '宁波银行',
				icon: 'icon-color-yinhang-ningbo',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 32,
				parent: 2
			}, {
				id: 33,
				name: '中信银行',
				icon: 'icon-color-yinhang-zhongxin',
				type: 1,
				create_time: '2024-03-20 10:31:31',
				sort: 33,
				parent: 2
			}

		]


		typeList.forEach(item => {
			item.pinyin = pinyin.chineseToPinYinFirst(item.name)
		})


		console.log('typeList', typeList)

		const dataStr = (typeList.map(item =>
			`(${item.id},'${item.name}','${item.icon}','${item.create_time}',${item.type},${item.sort},${item.parent},'${item.pinyin}')`
		)).join(',')
		// 插入类型数据
		await db.insertTableData('tb_asset_type', dataStr, col)
	} catch (e) {
		console.log(e)
		//TODO handle the exception
		return false
	}
	return true
}

// 查资产类型 existAssetFlag 入参为true则
export async function getAssetType(existAssetFlag, parent) {
	try {
		await openDB();
		let sqlCondition = ''
		if (existAssetFlag != null && existAssetFlag) {
			sqlCondition += ' AND (select 1 from tb_asset where tb_asset.type_id = t.id) '
		}

		if (parent != null) {
			sqlCondition = ` AND t.parent = ${parent}`
		}

		let sql =
			`SELECT t.*,(select count(1) from tb_asset_type tmp where tmp.parent = t.id) as childNum from tb_asset_type t WHERE 1=1 ${sqlCondition} order by t.sort asc`

		return db.selectTableDataBySql(sql);

	} catch (e) {
		//TODO handle the exception
		return []
	} finally {
		await closeDB()
	}
}


export async function init() {
	await openDB();
	const bolType = await createType()
	const bolRecord = await createRecord()
	const bolAsset = await createAsset();
	const bolAssetType = await createAssetType();
	uni.hideLoading();
	if (bolType && bolRecord && bolAsset && bolAssetType) {
		uni.showToast({
			title: '初始化成功!',
		})

		uni.setStorageSync('isInitSqlite', '1');
	} else {
		uni.showModal({
			title: '提示',
			content: "初始化数据失败,请重新进入app",
			showCancel: false
		})
	}
	await closeDB()
}